﻿Module mdlReports
#Region "************************* Hospital Management Reports business logic *************************"

    Public Sub ShowInvoiceReport(ByVal PracticeId As Integer, ByVal PatientId As Integer, ByVal VisitDate As Date, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String

        Try
            st.Add("ReportName", "RptInvoice.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)
            Dim DateString As String = DateAndTime.MonthName(VisitDate.Month) & " " & VisitDate.Day & ", " & VisitDate.Year
            st.Add("VisitDate", DateString)

            strQuery = "Select ProcedureName,Quantity,tblCharges.Cost,Quantity * tblCharges.Cost  AS TotalCharge" & _
                        " FROM tblCharges INNER JOIN tblProcedures ON tblCharges.ProcedureTypeId=tblProcedures.ProcedureId" & _
                        " INNER JOIN tblVisits ON tblVisits.VisitId=tblCharges.VisitId" & _
                        " WHERE tblVisits.PatientId=" & PatientId & _
                        " AND VisitDate BETWEEN '" & VisitDate.ToShortDateString & " 00:00' AND '" & _
                        VisitDate.ToShortDateString & " 23:59' AND tblVisits.PracticeId=" & PracticeId & _
                        " Union All " & _
                        " Select ItemName [ProcedureName],Quantity,IsNull(ChargeToPatient,0) AS Cost, " & _
                        " Quantity * Isnull(ChargeToPatient,0) AS TotalCharge From tblInventoryIssue INNER JOIN " & _
                        " tblItem ON tblInventoryIssue.ItemId=tblItem.ItemId INNER JOIN " & _
                        " tblVisits ON tblVisits.VisitId=tblInventoryIssue.VisitId" & _
                        " Where tblVisits.PatientId=" & PatientId & _
                        " And Visitdate Between '" & VisitDate.ToShortDateString & " 00:00' AND '" & _
                        VisitDate.ToShortDateString & " 23:59' AND tblVisits.PracticeId=" & PracticeId

            dsTemp = FillDataSet(strQuery, "repMain")
            Dim TotalCharge As Single
            For i As Integer = 0 To dsTemp.Tables(0).Rows.Count - 1
                TotalCharge = TotalCharge + dsTemp.Tables(0).Rows(i).Item("TotalCharge")
            Next
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT   PaymentType ,Payment" & _
                        " FROM   tblPayment" & _
                        " INNER JOIN  LtblPaymentType ON tblPayment.PaymentTypeId = LtblPaymentType.PaymentTypeId" & _
                        "  WHERE PatientId=" & PatientId & " AND PracticeId=" & PracticeId & _
                        " AND PaymentDate BETWEEN '" & VisitDate.ToShortDateString & " 00:00' AND '" & _
                        VisitDate.ToShortDateString & " 23:59'"

            dsTemp = FillDataSet(strQuery, "repPaymentd")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT 'Dr. ' + tblUsers.FirstName + ' ' + tblUsers.LastName AS Name" & _
                        ", tblPracticeInfo.Address1, tblPracticeInfo.Address2, LtblTown.Town, LtblParish.Parish" & _
                        ", LtblCountry.Country, 'Phone No. ' + tblPracticeInfo.PhoneNo AS PhoneNo" & _
                        ", 'Fax No. ' + tblPracticeInfo.FaxNo AS FaxNo" & _
                        " FROM tblPracticeInfo" & _
                        " INNER JOIN LtblParish ON tblPracticeInfo.ParishId = LtblParish.ParishId" & _
                        " INNER JOIN LtblTown ON tblPracticeInfo.TownId = LtblTown.TownId" & _
                        " INNER JOIN tblUsers ON tblUsers.UserId = tblPracticeInfo.UserId" & _
                        " INNER JOIN LtblCountry ON LtblCountry.CountryId=tblPracticeInfo.CountryId" & _
                        " WHERE tblPracticeInfo.PracticeId=" & PracticeId

            dsTemp = FillDataSet(strQuery, "repPracticeHeader")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) AS Name" & _
                        ", tblPatientInfo.Address1 AS Address1, tblPatientInfo.Address2 AS Address2" & _
                        ",  LtblTown.Town AS Town, LtblParish.Parish AS Parish, LtblCountry.Country AS Country" & _
                        " FROM tblPatientInfo INNER JOIN LtblParish ON tblPatientInfo.ParishId = LtblParish.ParishId" & _
                        " INNER JOIN LtblTown ON tblPatientInfo.TownId = LtblTown.TownId" & _
                        " INNER JOIN LtblCountry ON LtblCountry.CountryId = tblPatientInfo.CountryId" & _
                        " WHERE tblPatientInfo.PatientId=" & PatientId

            dsTemp = FillDataSet(strQuery, "repPatientInfo")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            'get Total Payment for this patient.
            Dim TotalPayment As Double = Val(strRecordID("tblPayment", "SUM(Payment)", "PatientId=" & PatientId & " AND PracticeId=" & PracticeId & " AND PaymentDate BETWEEN '" & VisitDate.ToShortDateString & " 00:00' AND '" & VisitDate.ToShortDateString & " 23:59'"))
            st.Add("TotalPayment", Format(Val(TotalPayment), "$0.00"))
            Dim TotalBalance As Double = Val(TotalCharge) - Val(TotalPayment)
            st.Add("TotalBalance", Format(Val(TotalBalance), "$0.00"))

            Call ConfigureReport(st, dsReport, "Invoice", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowVisitSummaryReport(ByVal intPatientId As Integer, ByVal frmMdi As Form)
        Try
            Dim st As New SortedList()
            Dim dsTemp As DataSet
            Dim dsReport As New DataSet
            Dim strQuery As String

            st.Add("ReportName", "RptVisitSummary.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            strQuery = "SELECT distinct tblVisits.VisitDate,tblVisits.PresentingComplaint, tblVisitResult.Therapy, tblVisitDiagnosis.Diagnosis, " & _
                       "tblVisits.VisitId FROM tblVisits LEFT OUTER JOIN  tblVisitResult ON tblVisits.VisitId = tblVisitResult.VisitId LEFT OUTER JOIN  " & _
                       "tblVisitDiagnosis ON tblVisits.VisitId = tblVisitDiagnosis.VisitId WHERE tblVisits.PatientId=" & intPatientId & _
                       " AND tblVisits.PracticeId=" & GetPracticeId(gintUserID) & "  ORDER BY tblVisits.VisitDate"
            dsTemp = FillDataSet(strQuery, "VisitSummary")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT  tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) AS Name, " & _
                       "tblPatientInfo.Address1 as Address1, tblPatientInfo.Address2 as Address2, " & _
                       "LtblParish.Parish as Parish, LtblTown.Town as Town, LtblCountry.Country as Country FROM  " & _
                       "tblPatientInfo INNER JOIN LtblParish ON tblPatientInfo.ParishId = LtblParish.ParishId " & _
                       "INNER JOIN  LtblTown ON tblPatientInfo.TownId = LtblTown.TownId  INNER JOIN LtblCountry ON " & _
                       "tblPatientInfo.CountryId = LtblCountry.CountryId  WHERE PatientId=" & intPatientId
            dsTemp = FillDataSet(strQuery, "PatientHeader")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            Call ConfigureReport(st, dsReport, "Visit Summary", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Call AddErrorLog("Error " & Err.Number & " (" & Err.Description & ") in Procedure " & _
                 Reflection.MethodBase.GetCurrentMethod.Name)
        End Try
    End Sub

    Public Sub ShowReceiptReport(ByVal PracticeId As Integer, ByVal PatientId As Integer, ByVal VisitDate As Date, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String

        Try
            st.Add("ReportName", "RptReceipt.rpt")
            st.Add("SoftwareName", gstrSoftware)
            Dim DateString As String = DateAndTime.MonthName(VisitDate.Month) & " " & VisitDate.Day & ", " & VisitDate.Year
            st.Add("VisitDate", DateString)

            strQuery = "SELECT   PaymentType ,Payment FROM   tblPayment INNER JOIN  LtblPaymentType ON " & _
                        "tblPayment.PaymentTypeId = LtblPaymentType.PaymentTypeId  WHERE PatientId=" & _
                        PatientId & " AND PracticeId=" & PracticeId & " AND PaymentDate BETWEEN '" & _
                        VisitDate.ToShortDateString & " 00:00' AND '" & VisitDate.ToShortDateString & " 23:59'"

            dsTemp = FillDataSet(strQuery, "Receipt")
            'If dsTemp.Tables(0).Rows.Count = 0 Then Exit Sub

            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT 'Dr. ' + tblUsers.FirstName + ' ' + tblUsers.LastName AS Name, tblPracticeInfo.Address1," & _
                        " tblPracticeInfo.Address2, LtblTown.Town, LtblParish.Parish, LtblCountry.Country, " & _
                        "'Phone No. ' + tblPracticeInfo.PhoneNo AS PhoneNo, 'Fax No. ' + tblPracticeInfo.FaxNo AS FaxNo " & _
                        "FROM tblPracticeInfo INNER JOIN LtblParish ON tblPracticeInfo.ParishId = LtblParish.ParishId " & _
                        "INNER JOIN LtblTown ON tblPracticeInfo.TownId = LtblTown.TownId INNER JOIN tblUsers ON " & _
                        "tblUsers.UserId = tblPracticeInfo.UserId INNER JOIN LtblCountry ON tblPracticeInfo.CountryId=LtblCountry.CountryId " & _
                        "WHERE tblPracticeInfo.PracticeId=" & PracticeId

            dsTemp = FillDataSet(strQuery, "repPracticeHeader")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) AS Name, tblPatientInfo.Address1 AS Address1, " & _
                        "tblPatientInfo.Address2 AS Address2,  LtblTown.Town AS Town, LtblParish.Parish AS Parish, LtblCountry.Country AS Country " & _
                        "FROM tblPatientInfo INNER JOIN LtblParish ON tblPatientInfo.ParishId = LtblParish.ParishId INNER JOIN LtblTown ON " & _
                        "tblPatientInfo.TownId = LtblTown.TownId INNER JOIN LtblCountry ON LtblCountry.CountryId = tblPatientInfo.CountryId " & _
                        "WHERE tblPatientInfo.PatientId=" & PatientId

            dsTemp = FillDataSet(strQuery, "repPatientInfo")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            Call ConfigureReport(st, dsReport, "Receipt", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowOutStandingBalanceReport(ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim ds As New DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String

        Try
            st.Add("ReportName", "RptOutstandingBalance.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            Dim DoctorCriteria As String
            If gintUserLevelId = 1 OrElse gintUserLevelId = 2 Then
                DoctorCriteria = " WHERE tblVisits.PracticeId=" & GetPracticeId(gintUserID) & " "
            Else
                DoctorCriteria = String.Empty
            End If

            'strQuery = "SELECT tblPatientInfo.PatientId, tblPatientInfo.FirstName + IsNull(' ' + tblPatientInfo.MiddleName,'') + ' ' + tblPatientInfo.LastName AS Name, SUM(tblCharges.Quantity * tblProcedures.Cost) AS TotalCharge FROM tblCharges INNER JOIN tblProcedures ON tblCharges.ProcedureTypeId = tblProcedures.ProcedureId INNER JOIN tblVisits ON tblCharges.VisitId = tblVisits.VisitId INNER JOIN tblPatientInfo ON tblVisits.PatientId = tblPatientInfo.PatientId " & DoctorCriteria & " GROUP BY tblPatientInfo.FirstName,tblPatientInfo.MiddleName,tblPatientInfo.LastName,tblPatientInfo.PatientId"
            strQuery = "SELECT tblPatientInfo.PatientId, tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) AS Name, SUM(tblCharges.Quantity * tblCharges.Cost) AS TotalCharge FROM tblCharges INNER JOIN tblProcedures ON tblCharges.ProcedureTypeId = tblProcedures.ProcedureId INNER JOIN tblVisits ON tblCharges.VisitId = tblVisits.VisitId INNER JOIN tblPatientInfo ON tblVisits.PatientId = tblPatientInfo.PatientId " & DoctorCriteria & " GROUP BY tblPatientInfo.FirstName,tblPatientInfo.LastName,tblPatientInfo.MiddleName,tblPatientInfo.PatientId,tblPatientInfo.PatientTitle"

            dsTemp = FillDataSet(strQuery, "Charge")
            ds.Tables.Add(dsTemp.Tables(0).Copy)
            If gintUserLevelId = 1 OrElse gintUserLevelId = 2 Then
                DoctorCriteria = " WHERE tblPayment.PracticeId=" & GetPracticeId(gintUserID) & " "
            Else
                DoctorCriteria = String.Empty
            End If

            strQuery = "SELECT PatientId, SUM(Payment) AS TotalPayment FROM tblPayment " & DoctorCriteria & " GROUP BY PatientId"

            dsTemp = FillDataSet(strQuery, "Payment")
            ds.Tables.Add(dsTemp.Tables(0).Copy)

            Dim dtBalance As New DataTable
            dtBalance.Columns.Add(New DataColumn("Name", GetType(String)))
            dtBalance.Columns.Add(New DataColumn("Balance", GetType(Single)))

            Dim i As Integer
            Dim TotalCharge As Single
            Dim TotalPayment As Single

            Dim Balance As Single
            Dim row As DataRow

            For i = 0 To ds.Tables("Charge").Rows.Count - 1
                TotalCharge = ds.Tables("Charge").Rows(i).Item("TotalCharge")
                If ds.Tables("Payment").Rows.Count <> 0 Then
                    If ds.Tables("Payment").Select("PatientId=" & ds.Tables("Charge").Rows(i).Item("PatientId")).Length > 0 Then
                        TotalPayment = ds.Tables("Payment").Select("PatientId=" & ds.Tables("Charge").Rows(i).Item("PatientId"))(0).Item("TotalPayment")
                    End If
                End If
                Balance = TotalPayment - TotalCharge
                'Balance = TotalCharge - TotalPayment
                If Balance <> 0 Then
                    row = dtBalance.NewRow
                    row.Item("Name") = ds.Tables("Charge").Rows(i).Item("Name")
                    row.Item("Balance") = Balance * -1
                    dtBalance.Rows.Add(row)
                End If
                'reset values
                TotalCharge = 0 : TotalPayment = 0 : Balance = 0
            Next

            dsReport.Tables.Add(dtBalance.Copy)
            Call ConfigureReport(st, dsReport, "Outstanding Balance", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowPatientStatementReport(ByVal PatientId As Integer, ByVal FromDate As Date, ByVal ToDate As Date, ByVal IsAllDate As Boolean, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String
        Dim DateCriteria As String

        Try
            st.Add("ReportName", "RptPatientStatement.rpt")
            st.Add("SoftwareName", gstrSoftware)
            Dim DateString As String = IIf(Not IsAllDate, FromDate.ToShortDateString & " to " & ToDate.ToShortDateString, String.Empty)
            st.Add("DateHeader", DateString)
            st.Add("DateHeaderSub", DateString)
            Dim TotalBalance As Decimal = (Val(strRecordID("tblCharges Join tblVisits On (tblCharges.VisitId=tblVisits.VisitId)" & _
                                       " Join tblProcedures On (tblCharges.ProcedureTypeId=tblProcedures.ProcedureId)", _
                                       "Sum((tblCharges.Quantity*tblCharges.Cost))", "tblVisits.PatientID=" & PatientId)) _
                                       - Val(strRecordID("tblPayment", "Sum(tblPayment.Payment)", "tblPayment.PatientID=" & PatientId)))
            st.Add("TotalBalance", Format(Val(TotalBalance), "$0.00"))
            If IsAllDate Then
                DateCriteria = String.Empty
            Else
                DateCriteria = " AND tblVisits.VisitDate BETWEEN '" & FromDate.ToShortDateString & " 00:00' AND '" & ToDate.ToShortDateString & " 23:59'"
            End If
            strQuery = "SELECT  tblProcedures.ProcedureName, tblCharges.Quantity, tblCharges.Cost, tblCharges.Quantity * tblCharges.Cost AS TotalCharge,VisitDate FROM tblProcedures INNER JOIN tblCharges ON tblProcedures.ProcedureId = tblCharges.ProcedureTypeId INNER JOIN tblVisits ON tblCharges.VisitId = tblVisits.VisitId INNER JOIN tblPatientInfo ON tblVisits.PatientId = tblPatientInfo.PatientId WHERE  tblVisits.PatientId = " & PatientId & DateCriteria
            dsTemp = FillDataSet(strQuery, "PatientStatement")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') )  AS Name, tblPatientInfo.Address1 as Address1, tblPatientInfo.Address2 as Address2, LtblCountry.Country as Country,  LtblParish.Parish as Parish, LtblTown.Town as Town FROM  tblPatientInfo INNER JOIN LtblParish ON LtblParish.ParishId = tblPatientInfo.ParishId INNER JOIN LtblTown ON LtblParish.ParishId = LtblTown.ParishId  INNER JOIN LtblCountry ON LtblCountry.CountryId=tblPatientInfo.CountryId WHERE PatientId=" & PatientId
            dsTemp = FillDataSet(strQuery, "repPatientInfo")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)


            If IsAllDate Then
                DateCriteria = String.Empty
            Else
                DateCriteria = " AND tblPayment.PaymentDate BETWEEN '" & FromDate.ToShortDateString & " 00:00' AND '" & ToDate.ToShortDateString & " 23:59'"
            End If
            strQuery = "SELECT LtblPaymentType.PaymentType,tblPayment.Payment,PaymentDate FROM LtblPaymentType INNER JOIN tblPayment ON LtblPaymentType.PaymentTypeId = tblPayment.PaymentTypeId WHERE tblPayment.PatientId=" & PatientId & DateCriteria
            dsTemp = FillDataSet(strQuery, "Payment")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            Call ConfigureReport(st, dsReport, "Patient Statement", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowBloodPressureGraph(ByVal PatientId As Integer, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String

        Try
            st.Add("ReportName", "RptBloodPressure.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            strQuery = "SELECT tblVisitBPPulse.BPSupineS, tblVisitBPPulse.BPSupineD,convert(varchar(20),tblVisits.VisitDate,111) as [VisitDate] FROM tblVisitBPPulse INNER JOIN  tblVisits ON tblVisitBPPulse.VisitId = tblVisits.VisitId INNER JOIN tblPatientInfo ON tblVisits.PatientId = tblPatientInfo.PatientId WHERE tblVisits.PatientId=" & PatientId
            dsTemp = FillDataSet(strQuery, "BloodPressureData")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) AS Name, tblPatientInfo.Address1, tblPatientInfo.Address2, LtblTown.Town,  LtblParish.Parish, LtblCountry.Country FROM LtblTown INNER JOIN tblPatientInfo ON LtblTown.TownId = tblPatientInfo.TownId INNER JOIN LtblParish ON tblPatientInfo.ParishId = LtblParish.ParishId INNER JOIN LtblCountry ON LtblCountry.CountryId=tblPatientInfo.CountryId WHERE PatientId=" & PatientId
            dsTemp = FillDataSet(strQuery, "PatientInfo")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            Call ConfigureReport(st, dsReport, "Blood Pressure Graph", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowVisitDetailsReport(ByVal PracticeId As Integer, ByVal PatientId As Integer, ByVal VisitDate As Date, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String
        Dim VisitId As Integer

        Try
            st.Add("ReportName", "RptVisitDetails.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            strQuery = " SELECT     tblVisits.VisitId, tblVisits.VisitDate, UPPER(tblPatientInfo.LastName) + ', ' + tblPatientInfo.FirstName + IsNull( ' ' + tblPatientInfo.MiddleName,'')  AS Name, tblVisits.VisitDate AS VisitDate, " & _
                      " tblVisits.PresentingComplaint, tblVisits.PresentingComplaintHistory, tblVisitLocomotion.Spine, tblVisitLocomotion.Muscles, tblVisitLocomotion.Bones, " & _
                      " tblVisitLocomotion.Gait, tblVisitLocomotion.Joints, tblVisitNeurology.CranialNerve1, tblVisitNeurology.CranialNerve2, " & _
                      " tblVisitNeurology.CranialNerve346, tblVisitNeurology.CranialNerve7, tblVisitNeurology.CranialNerve8, tblVisitNeurology.CranialNerve9, " & _
                      " tblVisitNeurology.CranialNerve10, tblVisitNeurology.CranialNerve11, tblVisitNeurology.CranialNerve12, tblVisitNeurology.SensoryTouch, " & _
                      " tblVisitNeurology.SensoryPosition, tblVisitNeurology.SensoryTemperature, tblVisitNeurology.SensoryVibration, " & _
                      " tblVisitNeurology.CerebellumFingerToNose, tblVisitNeurology.CerebellumRapidlyRepeated, tblVisitNeurology.CerebellumHeelToChin, " & _
                      " tblVisitNeurology.CerebellumTandemWalking, tblVisitNeurology.ReflexRightB, tblVisitNeurology.ReflexRightT, tblVisitNeurology.ReflexRightABDup, " & _
                      " tblVisitNeurology.ReflexRightABDlow, tblVisitNeurology.ReflexRightK, tblVisitNeurology.ReflexRightA, tblVisitNeurology.ReflexRightP, " & _
                      " tblVisitNeurology.ReflexLeftB, tblVisitNeurology.ReflexLeftT, tblVisitNeurology.ReflexLeftABDup, tblVisitNeurology.ReflexLeftABDlow, " & _
                      " tblVisitNeurology.ReflexLeftK, tblVisitNeurology.ReflexLeftA, tblVisitNeurology.Other, tblVisitNeurology.ReflexLeftP,tblVisitNeurology.BulkText, " & _
                      " tblVisitNeurology.InvoluntaryMovement, tblVisitNeurology.ToneULRight, tblVisitNeurology.ToneULLeft, tblVisitNeurology.ToneLLRight, tblVisitNeurology.ToneLLLeft, " & _
                      " tblVisitNeurology.FingerAbdRight, tblVisitNeurology.FingerAbdLeft, tblVisitNeurology.FingerAddRight, tblVisitNeurology.FingerAddLeft, " & _
                      " tblVisitNeurology.WristFlexRight, tblVisitNeurology.WristFlexLeft, tblVisitNeurology.WristExtRight, tblVisitNeurology.WristExtLeft, " & _
                      " tblVisitNeurology.ElbowFlexRight, tblVisitNeurology.ElbowFlexLeft, tblVisitNeurology.ElbowExtRight, tblVisitNeurology.ElbowExtLeft, " & _
                      " tblVisitNeurology.KneeFlexRight, tblVisitNeurology.KneeFlexLeft, tblVisitNeurology.KneeExtRight,tblVisitNeurology.KneeExtLeft, " & _
                      " tblVisitNeurology.ShoulderFlexRight, tblVisitNeurology.ShoulderFlexLeft, tblVisitNeurology.ShoulderExtRight, tblVisitNeurology.ShoulderExtLeft, " & _
                      " tblVisitNeurology.ShoulderAbdRight, tblVisitNeurology.ShoulderAbdLeft, tblVisitNeurology.ShoulderAddRight, tblVisitNeurology.ShoulderAddLeft, " & _
                      " tblVisitNeurology.HipFlexRight, tblVisitNeurology.HipFlexLeft, tblVisitNeurology.HipExtRight, tblVisitNeurology.HipExtLeft, " & _
                      " tblVisitNeurology.HipAbdRight, tblVisitNeurology.HipAbdLeft, tblVisitNeurology.HipAddRight, tblVisitNeurology.HipAddLeft, " & _
                      " tblVisitNeurology.AnkleDorsiflexRight, tblVisitNeurology.AnkleDorsiflexLeft, tblVisitNeurology.AnklePlantarflexRight, tblVisitNeurology.AnklePlantarflexLeft, " & _
                      " tblVisitGastroIntestinal.Mouth, tblVisitGastroIntestinal.AbdomenInspection, tblVisitGastroIntestinal.AbdomenPalpitation, tblVisitGastroIntestinal.AbdomenPercussion, " & _
                      " tblVisitGastroIntestinal.AbdomenAuscultation, tblVisitGastroIntestinal.Rectum, tblVisitGastroIntestinal.Anus, " & _
                      " tblVisitGeneralAssessment.MucusMebrane1, tblVisitGeneralAssessment.MucusMebrane2, tblVisitGeneralAssessment.MucusMebrane3, " & _
                      " tblVisitGeneralAssessment.MucusMebrane4, tblVisitGeneralAssessment.Hydration, tblVisitGeneralAssessment.Oedema, " & _
                      " tblVisitGeneralAssessment.HandsArms, tblVisitGeneralAssessment.Nodes, tblVisitGeneralAssessment.Skin, " & _
                      " tblVisitGeneralAssessment.BreastCondition, tblVisitGeneralAssessment.Temperature, tblVisitGeneralAssessment.TemperatureUnits, tblVisitGeneralAssessment.Height, tblVisitGeneralAssessment.HeightUnitsId," & _
                      " tblVisitGeneralAssessment.Weight, tblVisitGeneralAssessment.WeightUnitsId, tblVisitGeneralAssessment.AbdominalGirth, tblVisitGeneralAssessment.AbdominalGirthUnitsID, tblVisitGeneralAssessment.HeadCircumference, tblVisitGeneralAssessment.HeadCircumferenceUnitsID," & _
                      " tblVisitGeneralAssessment.GeneralCondition, tblVisitGeneralAssessment.Nutrition, tblVisitGenitoUrinary.GentitaliaPalpitation, " & _
                      " tblVisitGenitoUrinary.GentitaliaInspection, tblVisitBPPulse.BPSupineS, tblVisitBPPulse.BPSittingS, tblVisitBPPulse.BPStandingS,tblVisitBPPulse.BPSupineD, tblVisitBPPulse.BPSittingD, tblVisitBPPulse.BPStandingD, " & _
                      " tblVisitBPPulse.Pulse, tblVisitBPPulse.PulseExcercise, tblVisitBPPulse.PulsePostExcercise, tblVisitCardiology.PulseRhythm, " & _
                      " tblVisitCardiology.PulseVolume, tblVisitCardiology.JVPHeight, tblVisitCardiology.JVPWave, tblVisitCardiology.HeartInspection, " & _
                      " tblVisitCardiology.ApexPosition, tblVisitCardiology.ApexCharacter, tblVisitCardiology.Thrill, tblVisitCardiology.S2, tblVisitCardiology.S1, " & _
                      " tblVisitCardiology.AddedSound, tblVisitCardiology.Murmurs, tblVisitCardiology.Arterial, tblVisitCardiology.Venous, tblVisitUrine.Colour, " & _
                      " tblVisitUrine.Protein, tblVisitUrine.Sugar, tblVisitUrine.Ketone, tblVisitUrine.Blood, tblVisitUrine.SpGravity, tblVisitRespiratory.Cough, " & _
                      " tblVisitRespiratory.Sputum, tblVisitRespiratory.RespiratoryEffort, tblVisitRespiratory.Nose, tblVisitRespiratory.Sinuses, tblVisitRespiratory.Tonsils, " & _
                      " tblVisitRespiratory.Pharnyx, tblVisitRespiratory.Trachea, tblVisitRespiratory.ChestInspection, tblVisitRespiratory.ChestPalpitation, " & _
                      " tblVisitRespiratory.ChestPercussion, tblVisitRespiratory.ChestAuscultation, tblVisitRespiratory.RespiratoryRate, tblVisitResult.Therapy " & _
                      " FROM  tblVisits INNER JOIN " & _
                      " tblPatientInfo ON tblVisits.PatientId = tblPatientInfo.PatientId LEFT OUTER JOIN " & _
                      " tblVisitUrine ON tblVisits.VisitId = tblVisitUrine.VisitId LEFT OUTER JOIN " & _
                      " tblVisitNeurology ON tblVisits.VisitId = tblVisitNeurology.VisitId LEFT OUTER JOIN " & _
                      " tblVisitGeneralAssessment ON tblVisits.VisitId = tblVisitGeneralAssessment.VisitId LEFT OUTER JOIN " & _
                      " tblVisitGenitoUrinary ON tblVisits.VisitId = tblVisitGenitoUrinary.VisitId LEFT OUTER JOIN " & _
                      " tblVisitBPPulse ON tblVisits.VisitId = tblVisitBPPulse.VisitId LEFT OUTER JOIN " & _
                      " tblVisitCardiology ON tblVisits.VisitId = tblVisitCardiology.VisitId LEFT OUTER JOIN " & _
                      " tblVisitGastroIntestinal ON tblVisits.VisitId = tblVisitGastroIntestinal.VisitId LEFT OUTER JOIN " & _
                      " tblVisitLocomotion ON tblVisits.VisitId = tblVisitLocomotion.VisitId LEFT OUTER JOIN " & _
                      " tblVisitRespiratory ON tblVisits.VisitId = tblVisitRespiratory.VisitId LEFT OUTER JOIN " & _
                      " tblVisitResult ON tblVisits.VisitId = tblVisitResult.VisitId WHERE tblVisits.PatientId =" & PatientId & " AND PracticeId=" & PracticeId & " AND  tblVisits.VisitDate BETWEEN '" & VisitDate.ToShortDateString & " 00:00' AND '" & VisitDate.ToShortDateString & " 23:59'"
            dsTemp = FillDataSet(strQuery, "MainReportTable")
            If dsTemp.Tables("MainReportTable").Rows.Count = 0 Then
                'MessageBox.Show("There is no visit for selected date.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            Else
                VisitId = dsTemp.Tables("MainReportTable").Rows(0).Item("VisitId")
            End If
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = "SELECT  Test, TestType FROM tblVisitInvestigations WHERE VisitId=" & VisitId & " ORDER BY TestType "

            dsTemp = FillDataSet(strQuery, "InvestigativeReport")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            strQuery = " SELECT 'Dr. ' + tblSpecialists.Firstname + ' ' + tblSpecialists.Lastname AS Name " & _
                       " FROM tblVisitReferrals INNER JOIN tblSpecialists ON tblVisitReferrals.SpecialistId = tblSpecialists.DoctorId WHERE VisitId=" & VisitId

            dsTemp = FillDataSet(strQuery, "ReferralReport")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            Call ConfigureReport(st, dsReport, "Visit Details", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowPatientProfileReport(ByVal PracticeId As Integer, ByVal PatientId As Integer, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String

        Try
            st.Add("ReportName", "RptPatientProfile.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            strQuery = "SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) as Name, Sex, MaritalStatus, Address1,Address2,Parish,Town,LtblCountry.Country,HomePhone,WorkPhone,CAST(DATEPART(dd,DateOfBirth) AS VARCHAR)+'/'+ CAST(DATEPART(mm,DateOfBirth) AS VARCHAR)+'/'+ CAST(DATEPART(yyyy,DateOfBirth) AS VARCHAR) AS DateOfBirth,Occupation,InsuranceCompany,InsuranceNumber,InsuranceName,NextOfkinName,NextOfKinName,NextOfKinPhone,NextOfKinOccupation FROM tblPatientInfo INNER JOIN LtblSex ON tblPatientInfo.SexId = LtblSex.SexId INNER JOIN LtblMaritalStatus ON tblPatientInfo.MaritalStatusId = LtblMaritalStatus.MaritalStatusId INNER JOIN LtblParish ON tblPatientInfo.ParishId = LtblParish.ParishId INNER JOIN LtblTown ON tblPatientInfo.TownId = LtblTown.TownId INNER JOIN LtblCountry ON LtblCountry.CountryId=tblPatientInfo.CountryId   LEFT OUTER JOIN   LtblInsuranceCompany ON tblPatientInfo.InsuranceCompanyId = LtblInsuranceCompany.InsuranceCompanyId WHERE PatientId=" & PatientId

            dsTemp = FillDataSet(strQuery, "MainReportTable")
            dsReport.Tables.Add(dsTemp.Tables(0).Copy)

            Call ConfigureReport(st, dsReport, "Patient Profiles", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub GetPatientData(ByRef Name As String, ByRef Address1 As String, ByRef Address2 As String, ByRef TownParish As String, ByRef Country As String, ByRef DateOfBirth As DateTime, ByRef Sex As Integer, ByVal intPatientId As Integer)
        'get patientname ,address from patinet Id
        Dim dsPatient As DataSet = FillDataSet("SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) AS Name,Address1,Address2,Town,Parish,Country,SexId,DateOfBirth FROM (tblPatientInfo JOIN LtblTown ON tblPatientInfo.TownId=LtblTown.TownId) JOIN LtblParish ON tblPatientInfo.ParishId=LtblParish.ParishId INNER JOIN LtblCountry ON LtblCountry.CountryId=tblPatientInfo.CountryId WHERE PatientId = " & intPatientId)
        If dsPatient.Tables(0).Rows.Count > 0 Then
            With dsPatient.Tables(0).Rows(0)
                Name = .Item("Name") & ""
                Address1 = .Item("Address1") & ""
                Address2 = .Item("Address2") & ""
                TownParish = .Item("Town") & "" & ", " & .Item("Parish") & ""
                Country = .Item("Country") & ""
                If Not IsDBNullOrNothing(.Item("DateOfBirth")) Then
                    DateOfBirth = .Item("DateOfBirth")
                Else
                    DateOfBirth = Nothing
                End If

                Sex = .Item("SexId")
            End With
        End If
    End Sub

    Public Sub ShowGrowthReport2To20(ByVal PatientId As Integer, ByVal TableName As String, ByVal TableField As String, _
                               ByVal DataField As String, ByVal GroupField As String, ByVal ReportFileName As String, _
                               ByVal Caption As String, ByVal frmMDi As Form)

        Dim st As New SortedList()
        Dim dsReport As DataSet
        Dim dsTemp As DataSet
        Dim strQuery As String

        Dim Name As String = ""
        Dim Address1 As String = ""
        Dim Address2 As String = ""
        Dim TownParish As String = ""
        Dim Country As String = ""
        Dim Sex As Integer
        Dim DateOfBirth As DateTime
        Try
            st.Add("ReportName", ReportFileName)
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            GetPatientData(Name, Address1, Address2, TownParish, Country, DateOfBirth, Sex, PatientId) 'get data for patient,Pass values by reference

            st.Add("Name", Name)
            st.Add("Address1", Address1)
            st.Add("Address2", Address2)
            st.Add("TownParish", TownParish)
            st.Add("Country", Country)

            GroupField = " DATEDIFF(yy,'" & DateOfBirth & "', VisitDate) "

            DateOfBirth.AddHours(0).AddMinutes(0).AddSeconds(0)

            strQuery = "SELECT CAST(" & TableField & " AS INT) / 12 AS " & TableField & " ,Percentile5th,Percentile95th,-1 as UserData FROM " & TableName & " WHERE Sex=" & Sex & " and " & TableField & " >=24"
            dsReport = FillDataSet(strQuery, "MainReport")

            strQuery = "SELECT " & DataField & " as UserData," & GroupField & " AS AgeInMonth,tblVisits.VisitDate FROM (tblVisitGeneralAssessment INNER JOIN tblVisits ON tblVisitGeneralAssessment.VisitId=tblVisits.VisitId) INNER JOIN tblPatientInfo ON tblVisits.PatientId=tblPatientInfo.PatientId WHERE tblPatientInfo.PatientId=" & PatientId
            dsTemp = FillDataSet(strQuery)

            'strQuery = "SELECT " & DataField & " as UserData," & GroupField & " AS AgeInMonth,tblVisits.VisitDate FROM (tblVisitGeneralAssessment INNER JOIN tblVisits ON tblVisitGeneralAssessment.VisitId=tblVisits.VisitId) INNER JOIN tblPatientInfo ON tblVisits.PatientId=tblPatientInfo.PatientId WHERE tblPatientInfo.PatientId=" & PatientId
            'dsReport = FillDataSet(strQuery)

            Dim i As Integer
            For i = 0 To dsTemp.Tables(0).Rows.Count - 1
                dsReport.Tables(0).Select("Age=" & dsTemp.Tables(0).Rows(i).Item("AgeInMonth"))(0).Item("UserData") = dsTemp.Tables(0).Rows(i).Item("UserData")
            Next


            Call ConfigureReport(st, dsReport, Caption, frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Public Sub ShowBMIForAgeReport(ByVal PatientId As Integer, ByVal GroupField As String, ByVal ReportFileName As String, _
                                ByVal Caption As String, ByVal frmMDi As Form)

        Dim st As New SortedList()
        Dim dsReport As DataSet
        Dim dsTemp As DataSet
        Dim strQuery As String

        Dim Name As String = ""
        Dim Address1 As String = ""
        Dim Address2 As String = ""
        Dim TownParish As String = ""
        Dim Country As String = ""
        Dim Sex As Integer
        Dim DateOfBirth As DateTime
        Try
            st.Add("ReportName", ReportFileName)
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            GetPatientData(Name, Address1, Address2, TownParish, Country, DateOfBirth, Sex, PatientId) 'get data for patient,Pass values by reference

            st.Add("Name", Name)
            st.Add("Address1", Address1)
            st.Add("Address2", Address2)
            st.Add("TownParish", TownParish)
            st.Add("Country", Country)

            GroupField = " DATEDIFF(mm,'" & DateOfBirth & "', VisitDate) "

            DateOfBirth.AddHours(0).AddMinutes(0).AddSeconds(0)

            strQuery = "SELECT CAST(tblLengthForAge.Age AS INT) AS Age , " & _
                        "(tblWeightForAge.Percentile5th/(tblLengthForAge.Percentile5th * tblLengthForAge.Percentile5th)) as [Percentile5th], " & _
                        "(tblLengthForAge.Percentile95th/(tblLengthForAge.Percentile95th * tblLengthForAge.Percentile95th)) as [Percentile95th], " & _
                        "-1 as UserData FROM tblWeightForAge INNER JOIN tblLengthForAge ON " & _
                        "tblLengthForAge.Age=tblWeightForAge.Age " & _
                        " and tblLengthForAge.Sex=tblWeightForAge.Sex WHERE tblLengthForAge.Sex=" & Sex
            dsReport = FillDataSet(strQuery, "MainReport")

            strQuery = "SELECT (tblVisitGeneralAssessment.Weight/(tblVisitGeneralAssessment.Height * tblVisitGeneralAssessment.Height)) as UserData," & GroupField & " AS AgeInMonth,tblVisits.VisitDate FROM (tblVisitGeneralAssessment INNER JOIN tblVisits ON tblVisitGeneralAssessment.VisitId=tblVisits.VisitId) INNER JOIN tblPatientInfo ON tblVisits.PatientId=tblPatientInfo.PatientId WHERE tblPatientInfo.PatientId=" & PatientId
            dsTemp = FillDataSet(strQuery)

            Dim i As Integer
            'format datatable
            For i = 0 To dsTemp.Tables(0).Rows.Count - 1
                dsReport.Tables(0).Select("Age=" & dsTemp.Tables(0).Rows(i).Item("AgeInMonth"))(0).Item("UserData") = dsTemp.Tables(0).Rows(i).Item("UserData")
            Next

            Call ConfigureReport(st, dsReport, Caption, frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

    Public Sub ShowGrowthReport(ByVal PatientId As Integer, ByVal TableName As String, ByVal TableField As String, _
                                ByVal DataField As String, ByVal GroupField As String, ByVal ReportFileName As String, _
                                ByVal Caption As String, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsReport As DataSet
        Dim dsTemp As DataSet
        Dim strQuery As String

        Dim Name As String = ""
        Dim Address1 As String = ""
        Dim Address2 As String = ""
        Dim TownParish As String = ""
        Dim Country As String = ""
        Dim Sex As Integer
        Dim DateOfBirth As DateTime
        Try
            st.Add("ReportName", ReportFileName)
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr. " & gstrUserName)

            GetPatientData(Name, Address1, Address2, TownParish, Country, DateOfBirth, Sex, PatientId) 'get data for patient,Pass values by reference

            st.Add("Name", Name)
            st.Add("Address1", Address1)
            st.Add("Address2", Address2)
            st.Add("TownParish", TownParish)
            st.Add("Country", Country)

            If TableName <> "tblWeightForLength" Then
                GroupField = " DATEDIFF(mm,'" & DateOfBirth & "', VisitDate) "

                DateOfBirth.AddHours(0).AddMinutes(0).AddSeconds(0)

                strQuery = "SELECT CAST(" & TableField & " AS INT) AS " & TableField & " ,Percentile5th,Percentile95th,-1 as UserData FROM " & TableName & " WHERE Sex=" & Sex
                dsReport = FillDataSet(strQuery, "MainReport")

                strQuery = "SELECT " & DataField & " as UserData," & GroupField & " AS AgeInMonth,tblVisits.VisitDate FROM (tblVisitGeneralAssessment INNER JOIN tblVisits ON tblVisitGeneralAssessment.VisitId=tblVisits.VisitId) INNER JOIN tblPatientInfo ON tblVisits.PatientId=tblPatientInfo.PatientId WHERE tblPatientInfo.PatientId=" & PatientId
                dsTemp = FillDataSet(strQuery)

                Dim i As Integer
                'format datatable
                For i = 0 To dsTemp.Tables(0).Rows.Count - 1
                    dsReport.Tables(0).Select("Age=" & dsTemp.Tables(0).Rows(i).Item("AgeInMonth"))(0).Item("UserData") = dsTemp.Tables(0).Rows(i).Item("UserData")
                Next

            Else

                'GroupField = " DATEDIFF(mm,'" & DateOfBirth & "', VisitDate) "

                'DateOfBirth.AddHours(0).AddMinutes(0).AddSeconds(0)

                strQuery = "SELECT CAST(" & TableField & " AS INT) AS " & TableField & " ,Percentile5th,Percentile95th,-1 as UserData FROM " & TableName & " WHERE Sex=" & Sex
                dsReport = FillDataSet(strQuery, "MainReport")

                strQuery = "SELECT " & DataField & " as UserData,Height AS LengthInWeight,tblVisits.VisitDate FROM (tblVisitGeneralAssessment INNER JOIN tblVisits ON tblVisitGeneralAssessment.VisitId=tblVisits.VisitId) INNER JOIN tblPatientInfo ON tblVisits.PatientId=tblPatientInfo.PatientId WHERE tblPatientInfo.PatientId=" & PatientId
                dsTemp = FillDataSet(strQuery)

                Dim i As Integer
                'format datatable
                For i = 0 To dsTemp.Tables(0).Rows.Count - 1
                    If Not IsDBNullOrNothing(dsTemp.Tables(0).Rows(i).Item("LengthInWeight")) AndAlso Val(dsTemp.Tables(0).Rows(i).Item("LengthInWeight")) > 0 Then
                        Dim drRow() As DataRow = dsReport.Tables(0).Select("Length=" & dsTemp.Tables(0).Rows(i).Item("LengthInWeight"))
                        If drRow.Length > 0 Then
                            drRow(0).Item("UserData") = dsTemp.Tables(0).Rows(i).Item("UserData")
                        End If
                    End If
                Next

            End If

            Call ConfigureReport(st, dsReport, Caption, frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowStatisticsReport(ByVal strQuery As String, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsReport As New DataSet

        Try
            st.Add("ReportName", "RptDrugUse.rpt")
            st.Add("SoftwareName", gstrSoftware)

            dsReport = FillDataSet(strQuery, "MainReport")
            If dsReport.Tables(0).Rows.Count = 0 Then
                MessageBox.Show("There is no data available for selected option(s).", gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            Call ConfigureReport(st, dsReport, "Practice Statistics", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowRevenueReport(ByVal strQuery As String, ByVal frmMdi As Form, ByVal rptName As String, Optional ByVal DtDate As String = "")
        Dim st As New SortedList()
        Dim dsReport As New DataSet

        Try
            If rptName = "Revenue By Month" Then
                st.Add("ReportName", "RptRevenueByMonthOnce.rpt")
            Else
                st.Add("ReportName", "RptRevenueByMonth.rpt")
            End If

            If DtDate <> "" Then
                st.Add("DateRangeHeader", DtDate)
            End If


            dsReport = FillDataSet(strQuery, "MainReport")
            If dsReport.Tables(0).Rows.Count = 0 Then
                MessageBox.Show("There is no data available for selected option(s).", gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            Call ConfigureReport(st, dsReport, rptName, frmMdi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowCauseOfDeathStatistic(ByVal strQuery As String, ByVal frmMdi As Form, Optional ByVal dtDate As String = "")
        Dim st As New SortedList()
        Dim dsReport As New DataSet

        Try
            st.Add("ReportName", "RptCauseOfDeathStatistics.rpt")
            st.Add("SoftwareName", gstrSoftware)
            If dtDate <> "" Then
                st.Add("DateRangeHeader", dtDate)
            End If


            dsReport = FillDataSet(strQuery, "MainReport")
            If dsReport.Tables(0).Rows.Count = 0 Then
                MessageBox.Show("There is no data available for selected option(s).", gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            Call ConfigureReport(st, dsReport, "Cause Of Death", frmMdi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowDiagnosisFrequency(ByVal strQuery As String, ByVal frmMdi As Form, ByVal strReportName As String, ByVal strReportCaption As String, Optional ByVal dtDate As String = "")
        Dim st As New SortedList()
        Dim dsReport As New DataSet

        Try
            st.Add("ReportName", strReportName)
            st.Add("SoftwareName", gstrSoftware)
            If dtDate <> "" Then
                st.Add("DateRangeHeader", dtDate)
            End If


            dsReport = FillDataSet(strQuery, "MainReport")
            If dsReport.Tables(0).Rows.Count = 0 Then
                MessageBox.Show("There is no data available for selected option(s).", gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            Call ConfigureReport(st, dsReport, strReportCaption, frmMdi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowPatientWaitingReport(ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsReport As New DataSet
        Dim strQuery As String
        Dim DoctorCriteria As String

        Try
            st.Add("ReportName", "RptPatients.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", IIf(gintUserLevelId = 1 OrElse gintUserLevelId = 2, "Dr. " & gstrUserName, String.Empty))
            st.Add("IsStatus", True)
            st.Add("RepHeader", "Patients Waiting")
            st.Add("DateHeader", Today.ToLongDateString)

            DoctorCriteria = IIf(gintUserLevelId = 1 OrElse gintUserLevelId = 2, " AND tblVisits.PracticeId=" & GetPracticeId(gintUserID), String.Empty)

            strQuery = "SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') )   as Name, VisitDate, VisitLocation, AppointmentType, PatientStatus FROM (((( tblVisits INNER JOIN tblPatientInfo ON tblVisits.PatientID = tblPatientInfo.PatientID) INNER JOIN LtblVisitLocation ON tblVisits.VisitLocationID = LtblVisitLocation.VisitLocationID) INNER JOIN LtblAppointmentType ON tblVisits.AppointmentTypeId = LtblAppointmentType.AppointmentTypeId) INNER JOIN LtblPatientStatus ON tblVisits.PatientStatusId = LtblPatientStatus.PatientStatusId) WHERE tblVisits.PatientStatusId = 4 AND tblVisits.VisitDate BETWEEN '" & Today.ToShortDateString & " 00:00' AND '" & Today.ToShortDateString & " 23:59'" & DoctorCriteria

            dsReport = FillDataSet(strQuery, "MainReport")

            Call ConfigureReport(st, dsReport, "Patient's Waiting", frmMDi)

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowDaysPatient(ByVal FromDate As Date, ByVal ToDate As Date, ByVal SelectType As Integer, ByVal frmMDi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String
        Dim DateString As String = ""
        Try

            If SelectType = 0 Or SelectType = 1 Then
                DateString = FromDate.ToShortDateString
            ElseIf SelectType = 2 Then
                DateString = FromDate.ToShortDateString & " to " & ToDate.ToShortDateString
            End If


            Dim DoctorCriteria As String
            If gintUserLevelId = 1 Or gintUserLevelId = 2 Then
                DoctorCriteria = " AND tblVisits.PracticeId=" & GetPracticeId(gintUserID)
            Else
                DoctorCriteria = String.Empty
            End If
            st.Add("ReportName", "RptPatients.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr." & gstrUserName)
            st.Add("DateHeader", DateString)
            strQuery = "SELECT tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) as Name, VisitDate, VisitLocation, AppointmentType, PatientStatus,(tblUsers.FirstName + ' ' + tblUsers.LastName ) as DoctorName  " & _
                        " FROM (((( tblVisits INNER JOIN tblPatientInfo ON tblVisits.PatientID = tblPatientInfo.PatientID) " & _
                        " INNER JOIN LtblVisitLocation ON tblVisits.VisitLocationID = LtblVisitLocation.VisitLocationID) " & _
                        " INNER JOIN LtblAppointmentType ON tblVisits.AppointmentTypeId = LtblAppointmentType.AppointmentTypeId) " & _
                        " INNER JOIN LtblPatientStatus ON tblVisits.PatientStatusId = LtblPatientStatus.PatientStatusId) INNER JOIN tblPracticeInfo   ON tblVisits.PracticeId=tblPracticeInfo.PracticeId INNER JOIN tblUsers ON tblUsers.UserId=tblPracticeInfo.UserId WHERE tblVisits.VisitDate BETWEEN '" & FromDate.ToShortDateString & " 00:00' AND '" & ToDate.ToShortDateString & " 23:59'" & DoctorCriteria

            dsTemp = FillDataSet(strQuery, "DayWise")

            Call ConfigureReport(st, dsTemp, "Day's Patient", frmMDi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowPrescriptionReport(ByVal VisitId As Integer, ByVal PatientId As Integer, ByVal frmMdi As Form)
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Dim dsTempPatient As DataSet
        Dim dsReport As New DataSet
        Dim strQuery As String
        Dim PatientName As String = ""
        Dim PatientAddress As String = ""
        Dim strDOB As String = ""
        Dim strAge As String = ""
        Try
            'PatientName = strRecordID("tblPatientInfo", "FirstName + IsNull(' ' + MiddleName,'') + IsNull(' ' + LastName,'') AS Name", "PatientId=" & PatientId)
            strQuery = "SELECT PatientTitle + ' ' + (IsNull(LastName + ', ' , '') + FirstName + Isnull(' ' + MiddleName, '') ) AS Name, " & _
                        "IsNull(Address1 + char(10),'') + IsNull(Address2 + char(10),'') + Town + char(10) + Parish + char(10) + Country as Address, " & _
                        "DateOfBirth FROM tblPatientInfo INNER JOIN LtblParish ON tblPatientInfo.ParishId = LtblParish.ParishId " & _
                        "INNER JOIN LtblTown ON tblPatientInfo.TownId = LtblTown.TownId " & _
                        "INNER JOIN LtblCountry ON tblpatientInfo.CountryId=LtblCountry.CountryId " & _
                        "WHERE tblPatientInfo.PatientId =" & PatientId
            dsTempPatient = FillDataSet(strQuery)
            If dsTempPatient.Tables(0).Rows.Count > 0 Then
                PatientName = dsTempPatient.Tables(0).Rows(0).Item("Name")
                PatientAddress = dsTempPatient.Tables(0).Rows(0).Item("Address")
                If Not IsDBNullOrNothing(dsTempPatient.Tables(0).Rows(0).Item("DateOfBirth")) Then
                    strDOB = Format(CDate(dsTempPatient.Tables(0).Rows(0).Item("DateOfBirth")), "dd-MMM-yyyy")
                End If
                If strDOB <> "" Then
                    If DateDiff(DateInterval.Year, CDate(strDOB), Today) <> 0 Then
                        strAge = DateDiff(DateInterval.Year, CDate(strDOB), Today) & " Years old." 'show the age of patient
                    Else
                        strAge = DateDiff(DateInterval.Month, CDate(strDOB), Today) & " Months old."
                    End If
                Else
                    strAge = ""
                End If
            End If
            st.Add("ReportName", "RptPrescription.rpt")
            st.Add("SoftwareName", gstrSoftware)
            st.Add("DoctorName", "Dr." & gstrUserName)
            st.Add("PatientName", PatientName)
            st.Add("PatientAddress", PatientAddress)
            st.Add("DOB", strDOB)
            st.Add("Age", strAge)
            strQuery = "SELECT LtblDrugs.DrugName as DrugName , tblVisitPrescription.DrugDose as DrugDose, LtblDrugDoseUnits.DrugDoseUnit as DrugDoseUnit, tblVisitPrescription.Frequency as Frequency,tblVisitPrescription.Comments FROM LtblDrugDoseUnits INNER JOIN tblVisitPrescription ON LtblDrugDoseUnits.DrugDoseUnitID = tblVisitPrescription.DrugDoseUnitId INNER JOIN LtblDrugs ON tblVisitPrescription.DrugId = LtblDrugs.DrugId WHERE VisitId=" & VisitId
            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "Prescription", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowAddressBook(ByVal PatientIds As String, ByVal frmMdi As Form)
        Dim strQuery As String
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            st.Add("ReportName", "RptAddressBook.rpt")
            strQuery = "Select tblPatientInfo.PatientTitle + ' ' + (IsNull(tblPatientInfo.LastName + ', ' , '') + tblPatientInfo.FirstName + Isnull(' ' + tblPatientInfo.MiddleName, '') ) as Name, tblPatientInfo.Address1, " & _
                        "tblPatientInfo.Address2,LtblTown.Town,LtblParish.Parish,LtblCountry.Country, " & _
                        "tblPatientInfo.WorkPhone, tblPatientInfo.HomePhone from " & _
                        "tblPatientInfo Inner Join LtblTown On tblPatientInfo.TownId=LtblTown.TownId " & _
                        "Inner Join LtblParish On  tblPatientInfo.ParishId=LtblParish.ParishId " & _
                        "Inner Join LtblCountry On  tblPatientInfo.CountryId=LtblCountry.CountryId " & _
                        "Where tblPatientInfo.Void = 0 and tblPatientInfo.PatientId In (" & PatientIds & ")" & _
                        " ORDER BY tblPatientInfo.LastName"
            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "Address Book", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowNoOfPatientsReport(ByVal frmDate As Date, ByVal toDate As Date, ByVal frmMdi As Form)
        Dim strQuery As String
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            st.Add("ReportName", "RptNoOfPatientsSeen.rpt")
            st.Add("DateRangeHeader", "From " & Format(frmDate, gstrDtFormat) & " - To " & Format(toDate, gstrDtFormat))
            strQuery = "SELECT convert(Varchar(10),VisitDate,111) as [VisitDate],Count(VisitId) PatientNo from tblVisits where VisitDate between '" & Format(frmDate, gstrDtFormat & " 00:00") & "' and '" & Format(toDate, gstrDtFormat & " 23:59") & "' Group by convert(Varchar(10),VisitDate,111) "
            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "Number Of Patients Seen", frmMdi)
        Catch ex As Exception

        End Try
    End Sub

    Public Sub ShowSickReport(ByVal VisitId As Integer, ByVal PatientId As Integer, ByVal frmMdi As Form, ByVal frmOpen As String)
        Dim strQuery As String
        Dim PatientName As String = ""
        Dim strQualification As String = ""
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            strQuery = "SELECT PatientTitle + ' ' + (FirstName + Isnull(' ' + MiddleName, '') + IsNull(' ' + LastName, '') ) as Name from tblPatientInfo where PatientId=" & PatientId
            dsTemp = FillDataSet(strQuery)
            If dsTemp.Tables(0).Rows.Count > 0 Then
                PatientName = dsTemp.Tables(0).Rows(0).Item("Name")
            End If
            dsTemp.Dispose()
            strQuery = "Select Qualifications from tblUsers where UserId=" & gintUserID
            dsTemp = FillDataSet(strQuery)
            If dsTemp.Tables(0).Rows.Count > 0 Then
                If IsDBNullOrNothing(dsTemp.Tables(0).Rows(0).Item("Qualifications")) Then
                    strQualification = ""
                Else
                    strQualification = dsTemp.Tables(0).Rows(0).Item("Qualifications")
                End If
            Else
                strQualification = ""
            End If
            dsTemp.Dispose()
            st.Add("ReportName", "RptSickLeave.rpt")
            st.Add("DoctorNameWithQualification", "Dr." & gstrUserName & ", " & strQualification)
            st.Add("PatientName", PatientName)
            strQuery = "SELECT tblVisitResult.SickStartDate,tblVisitResult.SickEndDate,tblVisitResult.TotalSickDays,tblVisits.VisitDate FROM " & _
                        "tblVisitResult INNER JOIN tblVisits on tblVisitResult.VisitId=tblVisits.VisitId and tblVisits.VisitId = " & VisitId
            dsTemp = FillDataSet(strQuery)
            If frmOpen <> "PatientInfo" Then
                Call ConfigureReport(st, dsTemp, "Sick Leave", frmMdi)
            Else
                Call ConfigureReport1(st, dsTemp, "Sick Leave", frmMdi)
            End If

        Catch ex As Exception

        End Try
    End Sub

#End Region

#Region "************************* Report Configuration business logic *************************"

    Private Sub ConfigureReport(ByVal stList As SortedList, ByVal dsReport As DataSet, ByVal rptCaption As String, ByVal frmMdi As Form)
        LoadCursor(frmMdi)
        Dim dsTemp As DataSet = FillDataSet("select tblUsers.FirstName + Isnull(' ' + tblusers.LastName,'') [Name], " & _
                                            "tblUsers.Qualifications,tblPracticeInfo.CompanyName,tblPracticeInfo.Address1, " & _
                                            "tblPracticeInfo.Address2,LtblParish.Parish,LtblTown.Town, " & _
                                            "LtblCountry.Country,tblPracticeInfo.PhoneNo,tblPracticeInfo.FaxNo, " & _
                                            "tblUsers.EmailId from tblPracticeInfo " & _
                                            " Inner Join tblUsers On tblPracticeInfo.UserId=tblUsers.UserId " & _
                                            "Inner Join LtblParish On tblPracticeInfo.ParishId=LtblParish.ParishId " & _
                                            "Inner Join LtblTown On tblPracticeInfo.TownId=LtblTown.TownId " & _
                                            "Inner Join LtblCountry On tblPracticeInfo.CountryId=LtblCountry.CountryId " & _
                                            "Where tblUsers.UserLevelId=1 order by tblUsers.UserId")
        If dsTemp.Tables(0).Rows.Count > 0 Then
            stList.Add("HeaderDoctorName", dsTemp.Tables(0).Rows(0).Item("Name") & " , " & Trim(dsTemp.Tables(0).Rows(0).Item("Qualifications") & ""))
            stList.Add("HeaderCompanyName", dsTemp.Tables(0).Rows(0).Item("CompanyName"))
            stList.Add("HeaderAddress1", dsTemp.Tables(0).Rows(0).Item("Address1") + " , " + dsTemp.Tables(0).Rows(0).Item("Address2"))
            stList.Add("HeaderAddress2", dsTemp.Tables(0).Rows(0).Item("Town") + " , " & dsTemp.Tables(0).Rows(0).Item("Parish"))
            stList.Add("HeaderAddress3", Trim(dsTemp.Tables(0).Rows(0).Item("Country") & ""))
            stList.Add("HeaderContactDetails", "Tel: " & dsTemp.Tables(0).Rows(0).Item("PhoneNo") & " , Fax: " & dsTemp.Tables(0).Rows(0).Item("FaxNo"))
            stList.Add("HeaderMailId", dsTemp.Tables(0).Rows(0).Item("EmailId"))
        End If

        Dim frmTemp As New frmUtilityCRViewer
        With frmTemp
            .ConfigureCrystalReports(stList, dsReport, False)
            '.crViewer.Zoom(100)
            .MdiParent = frmMdi
            '.crViewer.ShowCloseButton = True
            .Text = rptCaption
            .Dock = DockStyle.Fill
            .BringToFront()
            .Show()
            .Location = New Point(0, 0)
            .StartPosition = FormStartPosition.CenterScreen
        End With
        DestroyCrushor(frmMdi)
    End Sub


    Private Sub ConfigureReport1(ByVal stList As SortedList, ByVal dsReport As DataSet, ByVal rptCaption As String, ByVal frmMdi As Form)
        LoadCursor(frmMdi)
        Dim dsTemp As DataSet = FillDataSet("select tblUsers.FirstName + Isnull(' ' + tblusers.LastName,'') [Name], " & _
                                            "tblUsers.Qualifications,tblPracticeInfo.CompanyName,tblPracticeInfo.Address1, " & _
                                            "tblPracticeInfo.Address2,LtblParish.Parish,LtblTown.Town, " & _
                                            "LtblCountry.Country,tblPracticeInfo.PhoneNo,tblPracticeInfo.FaxNo, " & _
                                            "tblUsers.EmailId from tblPracticeInfo " & _
                                            " Inner Join tblUsers On tblPracticeInfo.UserId=tblUsers.UserId " & _
                                            "Inner Join LtblParish On tblPracticeInfo.ParishId=LtblParish.ParishId " & _
                                            "Inner Join LtblTown On tblPracticeInfo.TownId=LtblTown.TownId " & _
                                            "Inner Join LtblCountry On tblPracticeInfo.CountryId=LtblCountry.CountryId " & _
                                            "Where tblUsers.UserLevelId=1 order by tblUsers.UserId")
        If dsTemp.Tables(0).Rows.Count > 0 Then
            stList.Add("HeaderDoctorName", dsTemp.Tables(0).Rows(0).Item("Name") & " , " & Trim(dsTemp.Tables(0).Rows(0).Item("Qualifications") & ""))
            stList.Add("HeaderCompanyName", dsTemp.Tables(0).Rows(0).Item("CompanyName"))
            stList.Add("HeaderAddress1", dsTemp.Tables(0).Rows(0).Item("Address1") + " , " + dsTemp.Tables(0).Rows(0).Item("Address2"))
            stList.Add("HeaderAddress2", dsTemp.Tables(0).Rows(0).Item("Town") + " , " & dsTemp.Tables(0).Rows(0).Item("Parish"))
            stList.Add("HeaderAddress3", Trim(dsTemp.Tables(0).Rows(0).Item("Country") & ""))
            stList.Add("HeaderContactDetails", "Tel: " & dsTemp.Tables(0).Rows(0).Item("PhoneNo") & " , Fax: " & dsTemp.Tables(0).Rows(0).Item("FaxNo"))
            stList.Add("HeaderMailId", dsTemp.Tables(0).Rows(0).Item("EmailId"))
        End If

        Dim frmTemp As New frmUtilityCRViewer
        With frmTemp
            .ConfigureCrystalReports(stList, dsReport, False)
            '.crViewer.Zoom(100)
            '.MdiParent = frmMdi
            '.crViewer.ShowCloseButton = True
            .Text = rptCaption
            .Dock = DockStyle.Fill
            .BringToFront()
            .ShowDialog()
            .Location = New Point(0, 0)
            .StartPosition = FormStartPosition.CenterScreen
        End With
        DestroyCrushor(frmMdi)
    End Sub
#End Region

#Region "************************* Inventory Reports Logic *************************"

    Public Sub ShowFixedAssetsInventoryReport(ByVal frmMdi As Form)
        Dim strQuery As String
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            st.Add("ReportName", "RptInventoryFixedAssets.rpt")
            strQuery = "SELECT FixedAssetId,CategoryId,Category,Description,Location,Model" & _
                        " ,ModelNo,SerialNo,PurchasePrice,DepreciationPercentage,TodaysValue" & _
                        " FROM vwFixedAssetDetails"

            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "Fixed Assets Inventory By Category", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowFixedAssetListingReport(ByVal frmMdi As Form)
        Dim strQuery As String
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            st.Add("ReportName", "RptInventoryFixedAssetListing.rpt")
            strQuery = "SELECT FixedAssetId,CategoryId,Category,Description,Location,ManufacturerName" & _
                        " ,PurchaseDate,Model,ModelNo,SerialNo" & _
                        " FROM vwFixedAssetDetails"

            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "Fixed Asset Listing", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowVendorListingReport(ByVal VendorIds As String, ByVal frmMdi As Form)
        Dim strQuery As String
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            st.Add("ReportName", "RptInventoryVendorListing.rpt")
            strQuery = "Select VendorId,VendorName,Address1,Address2,Town,Parish,Country" & _
                        " ,Phone1,Phone2,ZipCode,Email,Website,FaxNo,MobileNo" & _
                        " From vwVendorDetails Where VendorId in (" & VendorIds & ")"

            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "Vendor Listing", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowItemstobeReorderReport(ByVal CatId As Integer, ByVal frmMdi As Form)
        Dim strQuery As String
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            st.Add("ReportName", "RptInventoryItemstobeReorder.rpt")
            strQuery = "Select ItemId,ItemCode,BarCodeNo,ItemName" & _
                        " ,Category,ManufacturerName,VendorName,PurchaseUnit" & _
                        " ,PurchaseUnitCost,ReOrderPoint,QOH,ReOrderQty" & _
                        " From vwItemDetails Where IsNull(QOH,0)<=IsNull(ReOrderPoint,0)" & _
                        IIf(CatId <= 0, "", " And CategoryId=" & CatId)

            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "Items to be Re-order", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Public Sub ShowOnHandInventoryReport(ByVal CatId As Integer, ByVal frmMdi As Form)
        Dim strQuery As String
        Dim st As New SortedList()
        Dim dsTemp As DataSet
        Try
            st.Add("ReportName", "RptInventoryOnHandInventory.rpt")
            strQuery = "Select ItemId,ItemCode,BarCodeNo,ItemName" & _
                        " ,Category,ManufacturerName,VendorName,PurchaseUnit" & _
                        " ,QOH" & _
                        " From vwItemDetails Where IsNull(QOH,0)>0" & _
                        IIf(CatId <= 0, "", " And CategoryId=" & CatId)

            dsTemp = FillDataSet(strQuery)
            Call ConfigureReport(st, dsTemp, "On Hand Inventory", frmMdi)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, gstrMessage, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#End Region

End Module
